package model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import model.bean.Canbo;

public class CanBoDAO extends BaseDAO {
	// void getCalendarID
	public int getCanboID() {
		openDatabase();
		qry = "SELECT idCanBo FROM mockdb.canbo ORDER BY idCanBo DESC LIMIT 1";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			int id;
			while (rs.next()) {

				id = rs.getInt("idCanBo");
				int idCanbo = id + 1;
				return idCanbo;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return 1;
	}

	public boolean themCanBo(int idCanbo, String tencb, String sex,
			String date, String diachicq, String dienthoai, String email,
			String idcq, String idcv) {
		boolean success = false;
		openDatabase();
		qry = "INSERT INTO mockdb.canbo (idCanBo, TenCB, GioiTinh, NgaySinh, DiaChi, SDT, email, cqdieutra_idCQ, chucvu_idchucvu) VALUES ('"
				+ idCanbo
				+ "', N'"
				+ tencb
				+ "', "
				+ sex
				+ ", '"
				+ date
				+ "', N'"
				+ diachicq
				+ "', '"
				+ dienthoai
				+ "', N'"
				+ email
				+ "', '" + idcq + "', '" + idcv + "')";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			success = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return success;

	}

	public ArrayList<Canbo> dsCanbo() {
		openDatabase();
		ArrayList<Canbo> dsCanbo = new ArrayList<Canbo>();
		qry = "select * from mockdb.canbo inner join chucvu  on canbo.chucvu_idchucvu=chucvu.idchucvu "+ " "
				+ "" +" inner join cqdieutra on canbo.cqdieutra_idCQ = cqdieutra.idCQ";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			while (rs.next()) {
				Canbo cb = new Canbo();

				cb.setIdCanbo(rs.getString("idCanBo"));
				cb.setTenCanbo(rs.getString("TenCB"));
				cb.setNgaySinh(rs.getString("NgaySinh"));
				cb.setGioiTinh(rs.getString("GioiTinh"));
				cb.setDiaChi(rs.getString("DiaChi"));
				cb.setSDT(rs.getString("SDT"));
				cb.setEmail(rs.getString("email"));
				cb.setIdCoquan(rs.getString("cqdieutra_idCQ"));
				cb.setIdChucvu(rs.getString("chucvu_idchucvu"));
				cb.setTenChucvu(rs.getString("tenchucvu"));

				dsCanbo.add(cb);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return dsCanbo;
	}

	public boolean xoaCanbo(String id) {
		openDatabase();
		qry = "delete from mockdb.canbo where idCanBo = '" + id + "'";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public Canbo getCanbo(String id) {
		openDatabase();
		Canbo cb = new Canbo();
		qry = "select * from canbo inner join chucvu  on canbo.chucvu_idchucvu=chucvu.idchucvu inner "
				+ " "
				+ ""
				+ " join cqdieutra on cqdieutra.idCQ = canbo.cqdieutra_idCQ where idCanBo = '"
				+ id + "'";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();
			while (rs.next()) {

				cb.setIdCanbo(rs.getString("idCanBo"));
				cb.setTenCanbo(rs.getString("TenCB"));
				cb.setNgaySinh(rs.getString("NgaySinh"));
				cb.setGioiTinh(rs.getString("GioiTinh"));
				cb.setDiaChi(rs.getString("DiaChi"));
				cb.setSDT(rs.getString("SDT"));
				cb.setEmail(rs.getString("email"));
				cb.setIdCoquan(rs.getString("cqdieutra_idCQ"));
				cb.setIdChucvu(rs.getString("chucvu_idchucvu"));
				cb.setTenChucvu(rs.getString("tenchucvu"));
				cb.setTenCoquan(rs.getString("TenCQ"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cb;
	}

	public boolean capNhapCB(String idcb, String tencb, String sex,
			String date, String diachicq, String dienthoai, String email,
			String idcq, String idcv) {
		openDatabase();
		qry = "UPDATE mockdb.canbo SET TenCB = N'" + tencb + "',GioiTinh = "
				+ sex + " ,NgaySinh = '" + date + "',DiaChi = N'" + diachicq
				+ "',SDT = '" + dienthoai + "' ,email = N'" + email
				+ "',cqdieutra_idCQ =  " + idcq + " ,chucvu_idchucvu = " + idcv
				+ " WHERE idCanBo = " + idcb + "";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}
}
